package com.tony.mindhr.utils;

import com.tony.mindhr.model.*;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.util.MultiValueMap;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Title: POIUtils
 * @Auther: 皮蛋布丁
 * @Date: 2021/06/17/20:45
 * @Description: 生成excel类
 */
public class POIUtils {

    public static ResponseEntity<byte[]> employee2Excel(List<Employee> list) {
        //1、创建一个excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2、创建文档摘要
        workbook.createInformationProperties();
        //3、获取并配置文档摘要信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
            //文档类别
        docInfo.setCategory("员工信息");
            //设置管理员
        docInfo.setManager("Tony");
            //设置公司信息
        docInfo.setCompany("www.javaee.top");
        //4、获取文档摘要信息
        SummaryInformation summaryInfo = workbook.getSummaryInformation();
            //设置文档标题
        summaryInfo.setTitle("员工信息表");
            //设置作者
        summaryInfo.setAuthor("皮蛋布丁");
            //设置备注信息
        summaryInfo.setComments("本文档由皮蛋布丁提供");
        //5、创建样式
            //创建标题行的样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
            //背景颜色
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            //填充模式
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //创建日期格式
        HSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
        //创建sheet
        HSSFSheet sheet = workbook.createSheet("员工信息1");
        //region 设置列宽（几列，5个字符）
        sheet.setColumnWidth(0,5*256);
        sheet.setColumnWidth(1,12*256);
        sheet.setColumnWidth(2,10*256);
        sheet.setColumnWidth(3,5*256);
        sheet.setColumnWidth(4,12*256);
        sheet.setColumnWidth(5,20*256);
        sheet.setColumnWidth(6,10*256);
        sheet.setColumnWidth(7,10*256);
        sheet.setColumnWidth(8,16*256);
        sheet.setColumnWidth(9,12*256);
        sheet.setColumnWidth(10,15*256);
        sheet.setColumnWidth(11,20*256);
        sheet.setColumnWidth(12,16*256);
        sheet.setColumnWidth(13,14*256);
        sheet.setColumnWidth(14,14*256);
        sheet.setColumnWidth(15,12*256);
        sheet.setColumnWidth(16,8*256);
        sheet.setColumnWidth(17,20*256);
        sheet.setColumnWidth(18,20*256);
        sheet.setColumnWidth(19,15*256);
        sheet.setColumnWidth(20,8*256);
        sheet.setColumnWidth(21,25*256);
        sheet.setColumnWidth(22,14*256);
        sheet.setColumnWidth(23,15*256);
        sheet.setColumnWidth(24,15*256);
        sheet.setColumnWidth(25,15*256);
        // endregion

        //region 创建所有标题行
        //6、创建标题行
        HSSFRow row0 = sheet.createRow(0);
        //创建列
        HSSFCell cell0 = row0.createCell(0);
        //设置列名
        cell0.setCellValue("编号");
        //设置背景颜色
        cell0.setCellStyle(headerStyle);
        HSSFCell cell1 = row0.createCell(1);
        cell1.setCellStyle(headerStyle);
        cell1.setCellValue("姓名");
        HSSFCell cell2 = row0.createCell(2);
        cell2.setCellStyle(headerStyle);
        cell2.setCellValue("工号");
        HSSFCell cell3 = row0.createCell(3);
        cell3.setCellStyle(headerStyle);
        cell3.setCellValue("性别");
        HSSFCell cell4 = row0.createCell(4);
        cell4.setCellStyle(headerStyle);
        cell4.setCellValue("出生日期");
        HSSFCell cell5 = row0.createCell(5);
        cell5.setCellStyle(headerStyle);
        cell5.setCellValue("身份证号码");
        HSSFCell cell6 = row0.createCell(6);
        cell6.setCellStyle(headerStyle);
        cell6.setCellValue("婚姻状况");
        HSSFCell cell7 = row0.createCell(7);
        cell7.setCellStyle(headerStyle);
        cell7.setCellValue("民族");
        HSSFCell cell8 = row0.createCell(8);
        cell8.setCellStyle(headerStyle);
        cell8.setCellValue("籍贯");
        HSSFCell cell9 = row0.createCell(9);
        cell9.setCellStyle(headerStyle);
        cell9.setCellValue("政治面貌");
        HSSFCell cell10 = row0.createCell(10);
        cell10.setCellStyle(headerStyle);
        cell10.setCellValue("电子邮件");
        HSSFCell cell11 = row0.createCell(11);
        cell11.setCellStyle(headerStyle);
        cell11.setCellValue("电话号码");
        HSSFCell cell12 = row0.createCell(12);
        cell12.setCellStyle(headerStyle);
        cell12.setCellValue("联系地址");
        HSSFCell cell13 = row0.createCell(13);
        cell13.setCellStyle(headerStyle);
        cell13.setCellValue("所属部门");
        HSSFCell cell14 = row0.createCell(14);
        cell14.setCellStyle(headerStyle);
        cell14.setCellValue("职位");
        HSSFCell cell15 = row0.createCell(15);
        cell15.setCellStyle(headerStyle);
        cell15.setCellValue("职称");
        HSSFCell cell16 = row0.createCell(16);
        cell16.setCellStyle(headerStyle);
        cell16.setCellValue("聘用形式");
        HSSFCell cell17 = row0.createCell(17);
        cell17.setCellStyle(headerStyle);
        cell17.setCellValue("最高学历");
        HSSFCell cell18 = row0.createCell(18);
        cell18.setCellStyle(headerStyle);
        cell18.setCellValue("专业");
        HSSFCell cell19 = row0.createCell(19);
        cell19.setCellStyle(headerStyle);
        cell19.setCellValue("毕业院校");
        HSSFCell cell20 = row0.createCell(20);
        cell20.setCellStyle(headerStyle);
        cell20.setCellValue("在职状态");
        HSSFCell cell21 = row0.createCell(21);
        cell21.setCellStyle(headerStyle);
        cell21.setCellValue("入职日期");
        HSSFCell cell22 = row0.createCell(22);
        cell22.setCellStyle(headerStyle);
        cell22.setCellValue("转正日期");
        HSSFCell cell23 = row0.createCell(23);
        cell23.setCellStyle(headerStyle);
        cell23.setCellValue("合同期限(年)");
        HSSFCell cell24 = row0.createCell(24);
        cell24.setCellStyle(headerStyle);
        cell24.setCellValue("合同起始日期");
        HSSFCell cell25 = row0.createCell(25);
        cell25.setCellStyle(headerStyle);
        cell25.setCellValue("合同终止日期");
        //endregion

        //遍历数据
        for (int i = 0; i < list.size(); i++) {
            Employee employee = list.get(i);
            //创建行(去除表头)
            HSSFRow row = sheet.createRow(i + 1);
            //region 创建列
            row.createCell(0).setCellValue(employee.getId());
            row.createCell(1).setCellValue(employee.getName());
            row.createCell(2).setCellValue(employee.getWorkID());
            row.createCell(3).setCellValue(employee.getGender());
            HSSFCell c4 = row.createCell(4);
            c4.setCellStyle(dateCellStyle);
            c4.setCellValue(employee.getBirthday());
            row.createCell(5).setCellValue(employee.getIdCard());
            row.createCell(6).setCellValue(employee.getWedlock());
            row.createCell(7).setCellValue(employee.getNation().getName());
            row.createCell(8).setCellValue(employee.getNativePlace());
            row.createCell(9).setCellValue(employee.getPoliticsstatus().getName());
            row.createCell(10).setCellValue(employee.getEmail());
            row.createCell(11).setCellValue(employee.getPhone());
            row.createCell(12).setCellValue(employee.getAddress());
            row.createCell(13).setCellValue(employee.getDepartment().getName());
            row.createCell(14).setCellValue(employee.getJobLevel().getName());
            row.createCell(15).setCellValue(employee.getPosition().getName());
            row.createCell(16).setCellValue(employee.getEngageForm());
            row.createCell(17).setCellValue(employee.getTiptopDegree());
            row.createCell(18).setCellValue(employee.getSpecialty());
            row.createCell(19).setCellValue(employee.getSchool());
            row.createCell(20).setCellValue(employee.getWorkState());
            HSSFCell c21 = row.createCell(21);
            c21.setCellStyle(dateCellStyle);
            c21.setCellValue(employee.getBeginDate());
            HSSFCell c22 = row.createCell(22);
            c22.setCellStyle(dateCellStyle);
            c22.setCellValue(employee.getConversionTime());
            row.createCell(23).setCellValue(employee.getContractTerm());
            HSSFCell c24 = row.createCell(24);
            c24.setCellStyle(dateCellStyle);
            c24.setCellValue(employee.getBeginContract());
            HSSFCell c25 = row.createCell(25);
            c25.setCellStyle(dateCellStyle);
            c25.setCellValue(employee.getEndContract());
            //endregion
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders header = new HttpHeaders();
        try {
            header.setContentDispositionFormData("attachment",new String("员工表.xls".getBytes("UTF-8"),"ISO-8859-1"));
            header.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(),header, HttpStatus.CREATED);
    }

    /**
    * @Description: employee2Employ excel解析成员工集合
    * @Param: [file, allNations, allPoliticsstatus, allDepartments, allPositions, allJobLevels]
    * @return: java.util.List<com.tony.mindhr.model.Employee>
    * @Author: 皮蛋布丁
    * @Date: 2021/6/19 14:50
    */
    public static List<Employee> employee2Employ(MultipartFile file, List<Nation> allNations, List<Politicsstatus> allPoliticsstatus, List<Department> allDepartments, List<Position> allPositions, List<JobLevel> allJobLevels) {
        List<Employee> list = new ArrayList<>();
        try {
            //1、创建一个workbook对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            //2、获取workbook中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                //3、获取表单
                HSSFSheet sheet = workbook.getSheetAt(i);
                //4、获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) {
                        continue; //跳过标题行
                    }
                    //5、获取行
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;   //防止数据中间有空行
                    }
                    //6、获取列
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    Employee employee = new Employee();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            case STRING:
                                String cellValue = cell.getStringCellValue();
                                //根据列数决定如何处理
                                switch (k) {
                                    case 1:
                                        employee.setName(cellValue);
                                        break;
                                    case 2:
                                        employee.setWorkID(cellValue);
                                        break;
                                    case 3:
                                        employee.setGender(cellValue);
                                        break;
                                    case 5:
                                        employee.setIdCard(cellValue);
                                        break;
                                    case 6:
                                        employee.setWedlock(cellValue);
                                        break;
                                    case 7:
                                        //无需遍历
                                        //籍贯在数据库中存的是id，在excel是name
                                        //只需判断name一致就能获取到下标(重写name方法以及无参、有参构造方法)
                                        int nationIndex = allNations.indexOf(new Nation(cellValue));
                                        employee.setNationId(allNations.get(nationIndex).getId());
                                        break;
                                    case 8:
                                        employee.setNativePlace(cellValue);
                                        break;
                                    case 9:
                                        int politicsIndex = allPoliticsstatus.indexOf(new Politicsstatus(cellValue));
                                        employee.setPoliticId(allPoliticsstatus.get(politicsIndex).getId());
                                        break;
                                    case 10:
                                        employee.setEmail(cellValue);
                                        break;
                                    case 11:
                                        employee.setPhone(cellValue);
                                        break;
                                    case 12:
                                        employee.setAddress(cellValue);
                                        break;
                                    case 13:
                                        int departmentIndex = allDepartments.indexOf(new Department(cellValue));
                                        employee.setDepartmentId(allDepartments.get(departmentIndex).getId());
                                        break;
                                    case 14:
                                        int joblevelIndex = allJobLevels.indexOf(new JobLevel(cellValue));
                                        employee.setJobLevelId(allJobLevels.get(joblevelIndex).getId());
                                        break;
                                    case 15:
                                        int positionIndex = allPositions.indexOf(new Position(cellValue));
                                        employee.setPosId(allPositions.get(positionIndex).getId());
                                        break;
                                    case 16:
                                        employee.setEngageForm(cellValue);
                                        break;
                                    case 17:
                                        employee.setTiptopDegree(cellValue);
                                        break;
                                    case 18:
                                        employee.setSpecialty(cellValue);
                                        break;
                                    case 19:
                                        employee.setSchool(cellValue);
                                        break;
                                    case 20:
                                        employee.setWorkState(cellValue);
                                        break;
                                }
                                break;
                            default: {
                                //类型不是String类型的处理
                                switch (k) {
                                    case 4:
                                        employee.setBirthday(cell.getDateCellValue());
                                        break;
                                    case 21:
                                        employee.setBeginDate(cell.getDateCellValue());
                                        break;
                                    case 22:
                                        employee.setConversionTime(cell.getDateCellValue());
                                        break;
                                    case 23:
                                        employee.setContractTerm(cell.getNumericCellValue());
                                        break;
                                    case 24:
                                        employee.setBeginContract(cell.getDateCellValue());
                                        break;
                                    case 25:
                                        employee.setEndContract(cell.getDateCellValue());
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    list.add(employee);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return list;
    }
}
